package com.shiyi;


import java.sql.*;

import java.util.*;


public class all {
    public static final String url = "jdbc:mysql://rm-uf6v59d4fuzneh9mpko.mysql.rds.aliyuncs.com:3306/gbtest";
    public static final String user = "shiyidata";
    public static final String passWord = "Bestage1116";
    public static final String table = "xf_package";
//    public static final String table = "test";
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        //加载mysql驱动
//        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接
        Connection connection = DriverManager.getConnection(url, user, passWord);
        connection.setAutoCommit(false);
//        PreparedStatement preparedStatement = connection.prepareStatement("select  * from order_test")

//        System.out.println(preparedStatement.getFetchSize());
        //获取日期 次数
        Map<String, Integer> map = new LinkedHashMap<String, Integer>();
            PreparedStatement pcount = connection.prepareStatement("select  日期,count(*) 次数 from " +table +" where `增值费用` is null "+ " group by 日期 ");
            ResultSet count = pcount.executeQuery();
            while (count.next()){
                String data = count.getString("日期");
                String counts = count.getString("次数");
                map.put(data,Integer.parseInt(counts));

            }
            pcount.close();
            count.close();

            PreparedStatement truncate = connection.prepareStatement("truncate table xf_package1");
            truncate.execute();
            truncate.close();


        /**
         * 创建插入表
         */
        PreparedStatement create = connection.prepareStatement("create table if not exists xf_package1(\n" +
                "  `序号` varchar(255) ,\n" +
                "  `日期` varchar(255) ,\n" +
                "  `运单号码` varchar(255) ,\n" +
                "  `寄件地区` varchar(255),\n" +
                "  `到件地区` varchar(255) ,\n" +
                "  `对方公司名称` varchar(255) ,\n" +
                "  `实际重量` varchar(255) ,\n" +
                "  `计费重量` varchar(255) ,\n" +
                "  `产品类型` varchar(255) ,\n" +
                "  `付款方式` varchar(255) ,\n" +
                "  `费用(元)` varchar(255) ,\n" +
                "  `折扣/促销` varchar(255) ,\n" +
                "  `应付金额` varchar(255),\n" +
                "  `经手人` varchar(255),\n" +
                "  `增值费用` varchar(255),\n" +
                "  `始发地(省名)` varchar(255) ,\n" +
                "  `目的地(省名)` varchar(255) ,\n" +
                "  `包裹物流单号` varchar(255),\n" +
                "  `包裹物流公司` varchar(255),\n" +
                "  `包裹称重重量` varchar(255),\n" +
                "  `包裹标准重量` varchar(255),\n" +
                "    `运费` double,\n" +
                "    `插值` double\n" +
                ") ");
        create.execute();
        create.close();

        PreparedStatement pinsert = null;
        int i = 0;
        //便利日期
            for(String key:map.keySet()) {
//                System.out.println(key+ ":" + map.get(key));

                //获得以天为单位的数据
                PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where 日期 = '" + key + "'");
//                PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where 日期 = '09-01' ");
                ResultSet rdata = pdata.executeQuery();
//                System.out.println(key);
                //遍历每天的数据
                while (rdata.next()){
                    //获取每行数据
                    //序号hao  始发地(省名)origin_address  目的地(省名) destination 包裹称重重量weight  包裹标准重量standard_weight 计费重量bill_weight
                    //删除表


                    /**
                     * 计算逻辑
                     */
                    String hao = rdata.getString("序号");
                    String freight = rdata.getString("运单号码");
                    String adress = rdata.getString("寄件地区");
                    String endadress = rdata.getString("到件地区");
                    String company = rdata.getString("对方公司名称");
                    String weight_true= rdata.getString("实际重量");
                    String bill_weight = rdata.getString("计费重量");
                    String product_type = rdata.getString("产品类型");
                    String payment_method = rdata.getString("付款方式");
                    String cost = rdata.getString("费用(元)");
                    String reduce = rdata.getString("折扣/促销");
                    String cost_true = rdata.getString("应付金额");
                    String person = rdata.getString("经手人");
                    String cost_add = rdata.getString("增值费用");
                    String origin_address = rdata.getString("始发地(省名)");
                    String destination = rdata.getString("目的地(省名)");
//                    String order_num = rdata.getString("包裹物流单号");
//                    String company_logistics = rdata.getString("包裹物流公司");
                    Integer num = map.get(key);//次数
                    String weight = rdata.getString("包裹称重重量");
                    String standard_weight = rdata.getString("包裹标准重量");



//                    System.out.println(hao+ "     ");
                    if (null==cost_add || "同城退回".equals(cost_add))
                    {
//                        System.out.println(cost_add);
                        double v = compute.compute_price( hao ,product_type,origin_address,endadress, destination, cost_true,num, compute.compute_weight(hao,weight, standard_weight, bill_weight));
                        double vv=Double.parseDouble(cost_true)-v;
                        pinsert = connection.prepareStatement("insert into xf_package1(`序号`,`日期`,`运单号码`,`实际重量`,`计费重量`," +
                                "`应付金额`,`始发地(省名)`, `目的地(省名)`,`包裹称重重量`,`包裹标准重量`,`运费`,`插值`,`增值费用` )" +
                                " values ('" + hao + "','" + key + "','"+ freight + "','" + weight_true + "','" + bill_weight + "','" + cost_true + "','" + origin_address + "','" +
                                destination + "','" + weight + "','" + standard_weight + "'," + v  + "," + vv + ",'"+ cost_add+"')");
                        pinsert.addBatch();
                        i++;
                    }
                    else {
                        System.out.println("2");
                        pinsert = connection.prepareStatement("insert into xf_package1(`序号`,`日期`,`运单号码`,`实际重量`,`计费重量`," +
                                "`应付金额`,`始发地(省名)`, `目的地(省名)`,`包裹称重重量`,`包裹标准重量`,`运费`,`插值`,`增值费用`  )" +
                                " values ('" + hao + "','" + key + "','"+ freight + "','" + weight_true + "','" + bill_weight + "','" + cost_true + "','" + origin_address + "','" +
                                destination + "','" + weight + "','" + standard_weight + "'," + cost_true  + "," +0+",'"+ cost_add+"')");
                        pinsert.addBatch();
                        i++;
                    }
                    while (i>1000)
                    {
                        int[] ints = pinsert.executeBatch();
                        connection.commit();
                        System.out.println(ints.length);
                        i = 0;
                    }

                    /**
                     * 插入数据
                     */

//                    System.out.println( hao+" " + key + " " + origin_address+" "+" "+destination + " "+
//                            num + " " + weight + " " + standard_weight + " " + bill_weight +" " +
//                            compute.compute_price(origin_address, destination, num, compute.compute_weight(weight, standard_weight, bill_weight)));

//                    System.out.println("-----------------------------------------");
                }
                pinsert.executeBatch();

            }
        pinsert.close();








    }
}

